#!/usr/bin/env python
# -*- coding: utf-8 -*-

import sys
import dianputools
from sqlquery import *


def get_product_exchange(s, e):
    # the product exchanged
    dbh = dianputools.connect_to_dingdan()
#     query = 'select distinct productid, producttitle from t_orderproduct where settlementtime > "%s" and settlementtime < "%s"' % (dtstart, dtend)
    query = 'select distinct productid, producttitle from t_orderproduct op left outer join t_order o on o.orderid=op.orderid where o.createdate > "%s" and o.createdate < "%s"' % (s, e)
    exchanged_products = dianputools.query(dbh, query)
    return exchanged_products


def get_product_stats(dtstart, dtend, p):
    desc, nums = dianputools.get_product_info(dtstart, dtend, p)

    q = query_comment(dtstart, dtend, p)
    comment_desc, comment_stats = dianputools.get_comment_stats(q)

    return desc + comment_desc, nums + comment_stats


def get_product_flux(day, pid):
    dbh = dianputools.connect_to_50()
    flux_table = 'product_flux_day'
    q = 'select pv,visits,uv,new_uv,uv-new_uv from %s where stat_date="%s" and product_id="%s"' % (flux_table, day, pid)
    # print q
    field = ['pv', 'visits', 'productvisit', 'newuser', 'olduser']
    try:
        sumflux = dianputools.query(dbh, q)[0]
    except:
        sumflux = [0] * len(field)

    return field, sumflux


if __name__ == '__main__':
    if len(sys.argv) != 4:
        # print 'usage: %s year month day' % (sys.argv[0])
        # sys.exit(1)
        year, month, day = 2012, 4, 16
    else:
        year, month, day = map(int, sys.argv[1:])
    print year, month, day

    dtstart, dtend = dianputools.str_day_start_and_end(year, month, day)

    tablename = 'product_stats'
    products =  get_product_exchange(dtstart, dtend)

    DEBUG = False
    for p,title in products:
        fields = ['day', 'pid', 'title']
        values = [dtstart, p, title]

        xxdesc, xxflux = get_product_flux(dtstart, p)
        fields.extend(xxdesc)
        values.extend(xxflux)

        if DEBUG: print p, title
        desc, pstats = get_product_stats(dtstart, dtend, p)
        if DEBUG: print pstats

        fields.extend(desc)
        values.extend(pstats)

        dbh = dianputools.connect_to_50()
        dianputools.insertinto(dbh, tablename, fields, values)
